﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace ntc_permit_dept_hr
{
   public class DBAccesess
    {
        SqlConnection conn;

        public DBAccesess()
        {
            conn = ConnectionManager.GetConnection();
            
        }

        public DataSet getAllEmployee()
        {
           
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "select empID, firstName, lastName,gender,birthdate,NICNumber,Nationality,Address,ContactNumber,Email,EmployedOn,Designation,Department from dbo.Employee";

            SqlDataAdapter da = new SqlDataAdapter(newCmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "emp");

           conn.Close();

            return ds;
        }

       
        public bool addEmp(string eid,string fname, string lname, string gender,DateTime bd,string nic, string nation,string add, int cNo,string email,DateTime eNo,string designation,string dep,string ext,string uLevl,string uNmae,string password,string purl)
        {
            bool status = false;
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }

            SqlCommand newCmd = conn.CreateCommand();

            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "insert into dbo.Employee values('"+eid+"','"+fname+"','"+lname+"','"+gender+"','"+bd+"','"+nic  +"','"+nation+"','"+add+"','"+cNo+"','"+email+"','"+eNo+"','"+designation+"','"+dep+"','"+ext+"','"+uLevl+"','"+uNmae+"','"+password+"','"+purl+"')";

            newCmd.ExecuteNonQuery();

            status = true;

            return status;

        }

        public DataTable getEmpID()
        {
            DataTable dt = new DataTable();          

            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }

            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "select empID from dbo.Employee";
            SqlDataReader dr = newCmd.ExecuteReader();
            while (dr.Read())
            {
                dt.Rows.Add(dr["empID"]);
            }
            conn.Close();
            return dt;
        }


        public DataTable getEmpDate(string eid)
        {
            DataTable dt = new DataTable();
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }

            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "select * from dbo.Employee where empID='"+eid+"'";
            SqlDataReader dr = newCmd.ExecuteReader();
            while (dr.Read())
            {
                dt.Rows.Add(dr["empID"]);
                dt.Rows.Add(dr["firstName"]);
                dt.Rows.Add(dr["lastName"]);
                dt.Rows.Add(dr["gender"]);
                dt.Rows.Add(dr["birthdate"]);
                dt.Rows.Add(dr["NICNumber"]);
                dt.Rows.Add(dr["Nationality"]);
                dt.Rows.Add(dr["Address"]);
                dt.Rows.Add(dr["ContactNumber"]);
                dt.Rows.Add(dr["Email"]);
                dt.Rows.Add(dr["EmployedOn"]);
                dt.Rows.Add(dr["Designation"]);
                dt.Rows.Add(dr["Department"]);
                dt.Rows.Add(dr["Ext"]);
                dt.Rows.Add(dr["UserLevel"]);
                dt.Rows.Add(dr["Username"]);
                dt.Rows.Add(dr["password"]);
                dt.Rows.Add(dr["photourl"]);

            }
            conn.Close();
            return dt;

        }

        public bool updateEmp(string eid, string fname, string lname, string gender, DateTime bd, string nic, string nation, string add, int cNo, string email, DateTime eNo, string designation, string dep, string ext, string uLevl, string uName, string password, string purl)
        {
            bool status = false;
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }

            SqlCommand newCmd = conn.CreateCommand();

            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "update dbo.Employee set firstName='"+fname+"', lastName='"+lname+"',gender='"+gender+"',birthdate='"+bd+"',NICNumber='"+nic+"',Nationality='"+nation+"',Address='"+add+"',ContactNumber='"+cNo+"',Email='"+email+"',EmployedOn="+eNo+",Designation='"+designation+"',Deparment='"+dep+"',Ext='"+ext+"',UserLevel='"+uLevl+"',Username='"+uName+"',password='"+password+"',photourl='"+purl+"' where empID='"+eid+"'";

            newCmd.ExecuteNonQuery();

            status = true;

            return status;

        }



    }
}
